import time
import pymysql

from Model.environment import Environment


class Sql:

    status = None

    def __init__(self, env: Environment):
        self.conn = pymysql.Connect(
            host=env.sql_ip,
            port=env.sql_port,
            user=env.sql_user,
            password=env.sql_pwd,
            db=env.sql_Database
        )

        self.cursor = self.conn.cursor()

    def execute(self, sql_str, msg):
        print(f"执行数据库操作 {msg} ")
        print(f"数据库语句：")
        print(sql_str)
        self.cursor.execute(sql_str)
        self.fetchall = self.cursor.fetchall()
        print("数据库执行结果：")
        for row in self.fetchall:
            print(row)
        print(f"====={msg}===数据库操作结束======")

    def close(self):
        self.cursor.close()
        self.conn.close()

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def set_retrieve_expire(self, sales_order_no):
        # 设置预售计划回收时间马上过期
        time_str = time.strftime("%Y-%m-%d 00:00:00", time.localtime())
        sql_str = f"""
            UPDATE OrderPreSalePlans
            SET RecyclingTime='{time_str}'
            WHERE TransportOrderId IS NULL AND RecyclingTime IS NOT NULL AND SourceNo="{sales_order_no}"
        """
        self.execute(sql_str, msg="设置预售计划回收时间马上过期")
        fetchall = self.cursor.fetchall()
        print(f"设置预售计划回收时间马上过期结果：{fetchall}")
        if fetchall == []:
            self.status = "数据库操作预售计划回收时间马上过期---成功"
        else:
            self.status = "数据库操作预售计划回收时间马上过期---失败"

    def set_retrieve_release(self, sales_order_no):
        # 预售计划超时回收释放
        sql_str = f"""
            UPDATE OrderPreSalePlans 
            SET ReleaseTime=NULL
            WHERE TransportOrderId IS NULL AND RecyclingTime IS NOT NULL AND SourceNo="{sales_order_no}"
        """
        self.execute(sql_str, msg="预售计划超时回收释放")
        fetchall = self.cursor.fetchall()
        print(f"预售计划超时回收释放结果：{fetchall}")
        if fetchall == []:
            self.status = "数据库操作预售计划超时回收释放---成功"
        else:
            self.status = "数据库操作预售计划超时回收释放---失败"
